if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_update_list')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_update_list
go


create procedure bd_proc_schedule_update_list (@shift_info varchar(max),@ip varchar(50),@operator varchar(50)) as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;


	--  异常扑捉机制
		BEGIN TRY


		-- 开启事务
			BEGIN TRAN


				create table #shift_info(
				  id int identity,
				  user_no varchar(50) COLLATE Chinese_PRC_CI_AS,
				  user_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				  user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				  dept_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				  dept_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				  column_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				  column_value varchar(200)	COLLATE Chinese_PRC_CI_AS,
				  year_month varchar(50) COLLATE Chinese_PRC_CI_AS,
				  shift_id varchar(50) COLLATE Chinese_PRC_CI_AS,
				  shift_bh varchar(50) COLLATE Chinese_PRC_CI_AS,
				  shift_date date
				)

				--创建 返回不满足排班的人的原因
				create table #return_table(
				  user_serial VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				  user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				  user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				  dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				  error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				)


				insert into #shift_info(user_serial,column_name,column_value,year_month)
				select userSerial,columnName,columnValue,yearMonth from openjson(@shift_info)
				with (
				  userSerial varchar(50),
				  columnName varchar(50),
				  columnValue varchar(200),
				  yearMonth varchar(50)
				);



				-- 更新排班人员信息
				update a set a.user_no = isnull(b.user_no,''), a.user_name = isnull(b.user_lname,''), a.dept_name = isnull(c.dep_name,''), a.dept_serial = isnull(c.dep_serial,'')
				from #shift_info a
				left join dt_user b on a.user_serial = b.user_serial
				left join dt_dep c on b.user_dep = c.dep_serial


				-- 更新日期
				update #shift_info set shift_date = convert(varchar(10),concat(year_month,'-',substring(column_name,2,len(column_name)-1)),120)
				where isnull(column_name,'') <> '' and isnull(year_month,'') <> ''


				-- 更新班次信息
				update #shift_info set shift_bh = substring(column_value,4,(charindex('|',column_value)-3-1))
				where isnull(column_value,'') <> '' and isnull(column_value,'') <> '[X]'


				-- 更新班次ID
				update a set a.shift_id = b.id
				from #shift_info a
				inner join zt_banci b on a.shift_bh = b.bh


				-- 检测是否为封账状态
				insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
				select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,'该日期考勤已经封账,不能排班'
				from #shift_info  a
				inner join bd_kq_accounts b on a.user_serial = b.user_serial and year(a.shift_date) = b.year and month(a.shift_date) = b.month


				-- 删除已经封账的数据(以用户为单位)
				delete a
				from #shift_info a
				inner join #return_table b on a.user_serial = b.user_serial


				-- 删除数据详情表
				delete a
				from bd_kq_schedule_detail a
				inner join #shift_info b on a.user_serial = b.user_serial and a.shift_date = b.shift_date


				-- 插入数据详情表
				insert into bd_kq_schedule_detail(user_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2,shift_is_rest)
				select a.user_serial,a.shift_id,a.column_value,a.shift_date,isnull(c.sbsj,0),isnull(c.xbsj,0),isnull(c.sbks,0),isnull(c.xbjs,0),isnull(c.cdsj,0),isnull(c.ztsj,0),isnull(c.bjcd,0),isnull(c.bjzt,0),isnull(c.alld,0),isnull(c.allt,0),isnull(c.is_enjoyment_subsidized,0),isnull(c.middle_shift_start_time,0),isnull(c.middle_shift_end_time,0),isnull(c.night_shift_start_time,0),isnull(c.night_shift_end_time,0),isnull(c.repast_start_time,0),isnull(c.repast_end_time,0),isnull(c.repast_start_time2,0),isnull(c.repast_end_time2,0),iif(left(isnull(a.column_value,''),3) = '[X]',1,0)
				from #shift_info a
				inner join zt_banci b on a.shift_id = b.id
				inner join zt_shid c on b.bh = c.parent
				where c.lx = 0


				declare @sql varchar(max) = '';

				select @sql += concat('update kt_paiba set ',column_name,' = ''',column_value,''', gly_no = ''',@operator,''' where user_serial = ''',user_serial,''' and rq = ''',year_month,'''; ')
				from #shift_info

				print @sql;

				-- 执行更新排班表
				exec(@sql);
				set @result = @result + @@ROWCOUNT;

				-- 插入考勤日志
				insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,mrbc,gly_no,sj)
				select user_serial,shift_date,1,6,1,0,0,column_value,@operator,getdate()
				from #shift_info

				insert into wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
				select 13,0,concat(@operator,'修改',user_name,shift_date,'排班'),@operator,getdate(),'',@ip,''
				from #shift_info



				--创建一个游标
				declare my_cursor cursor for select user_serial,shift_date from #shift_info      --这是游标my_cursor的值，这里随便发挥看业务场景

				--打开游标
				open my_cursor                  --没什么好说的

					--变量
					declare @shift_date date;
					declare @user_serial varchar(200);

					--循环游标
					fetch next from my_cursor into @user_serial,@shift_date  --获取my_cursor的下一条数据，其中为两个字段分别赋值给@id,@name
					while @@FETCH_STATUS=0 begin --假如检索到了数据继续执行

					-- 调用考勤计算(排班4)
					EXEC bd_proc_timecard_extend @shift_date,@shift_date,@user_serial,4
					print(concat(@user_serial,' ',@shift_date)) --print()打印变量 随便发挥

					fetch next from my_cursor into @user_serial,@shift_date --获取下一条数据并赋值给变量

				end--关闭释放游标
				close my_cursor
				deallocate my_cursor



		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
			ROLLBACK TRAN;
		END CATCH


	select * from #return_table

--  清除临时表
  drop table #shift_info
  drop table #return_table


-- 打开打印受影响行数
SET NOCOUNT OFF;
END
go
